﻿
namespace Fayshine.VA.RIAService.Web
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.ComponentModel.DataAnnotations;
    using System.Data;
    using System.Linq;
    using System.ServiceModel.DomainServices.EntityFramework;
    using System.ServiceModel.DomainServices.Hosting;
    using System.ServiceModel.DomainServices.Server;
    using System.Data.Objects;


    // Implements application logic using the FayshineEntities context.
    // TODO: Add your application logic to these methods or in additional methods.
    // TODO: Wire up authentication (Windows/ASP.NET Forms) and uncomment the following to disable anonymous access
    // Also consider adding roles to restrict access as appropriate.
    // [RequiresAuthentication]
    [EnableClientAccess()]
    public class FayshineDomainService : LinqToEntitiesDomainService<FayshineEntities>
    {

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'areas' query.
        [Query(IsDefault = true)]
        public IQueryable<area> GetAreas()
        {
            return this.ObjectContext.areas.Include("gates");
        }

        public void InsertArea(area area)
        {
            if ((area.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(area, EntityState.Added);
            }
            else
            {
                this.ObjectContext.areas.AddObject(area);
            }
        }

        public void UpdateArea(area currentarea)
        {
            this.ObjectContext.areas.AttachAsModified(currentarea, this.ChangeSet.GetOriginal(currentarea));
        }

        public void DeleteArea(area area)
        {
            if ((area.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(area, EntityState.Deleted);
            }
            else
            {
                this.ObjectContext.areas.Attach(area);
                this.ObjectContext.areas.DeleteObject(area);
            }
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'areatypes' query.
        [Query(IsDefault = true)]
        public IQueryable<areatype> GetAreatypes()
        {
            return this.ObjectContext.areatypes;
        }

        public void InsertAreatype(areatype areatype)
        {
            if ((areatype.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(areatype, EntityState.Added);
            }
            else
            {
                this.ObjectContext.areatypes.AddObject(areatype);
            }
        }

        public void UpdateAreatype(areatype currentareatype)
        {
            this.ObjectContext.areatypes.AttachAsModified(currentareatype, this.ChangeSet.GetOriginal(currentareatype));
        }

        public void DeleteAreatype(areatype areatype)
        {
            if ((areatype.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(areatype, EntityState.Deleted);
            }
            else
            {
                this.ObjectContext.areatypes.Attach(areatype);
                this.ObjectContext.areatypes.DeleteObject(areatype);
            }
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'cameras' query.
        [Query(IsDefault = true)]
        public IQueryable<camera> GetCameras()
        {
            return this.ObjectContext.cameras.Include("gate");
        }

        public void InsertCamera(camera camera)
        {
            if ((camera.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(camera, EntityState.Added);
            }
            else
            {
                this.ObjectContext.cameras.AddObject(camera);
            }
        }

        public void UpdateCamera(camera currentcamera)
        {
            this.ObjectContext.cameras.AttachAsModified(currentcamera, this.ChangeSet.GetOriginal(currentcamera));
        }

        public void DeleteCamera(camera camera)
        {
            if ((camera.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(camera, EntityState.Deleted);
            }
            else
            {
                this.ObjectContext.cameras.Attach(camera);
                this.ObjectContext.cameras.DeleteObject(camera);
            }
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'gates' query.
        [Query(IsDefault = true)]
        public IQueryable<gate> GetGates()
        {
            return this.ObjectContext.gates.Include("area").Include("cameras");
        }

        public void InsertGate(gate gate)
        {
            if ((gate.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(gate, EntityState.Added);
            }
            else
            {
                this.ObjectContext.gates.AddObject(gate);
            }
        }

        public void UpdateGate(gate currentgate)
        {
            this.ObjectContext.gates.AttachAsModified(currentgate, this.ChangeSet.GetOriginal(currentgate));
        }

        public void DeleteGate(gate gate)
        {
            if ((gate.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(gate, EntityState.Deleted);
            }
            else
            {
                this.ObjectContext.gates.Attach(gate);
                this.ObjectContext.gates.DeleteObject(gate);
            }
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'passengerflows' query.
        [Query(IsDefault = true)]
        public IQueryable<passengerflow> GetPassengerflows()
        {
            return this.ObjectContext.passengerflows;
        }

        public IQueryable<passengerflow> GetRealTimePassengerflows(int areaID)
        {
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID == areaID);

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();

                return this.ObjectContext.passengerflows.Where(p => gateIDs.Contains(p.GateID) &&
                                                               p.DateTime.Value.Day == DateTime.Now.Day &&
                                                               p.DateTime.Value.Month == DateTime.Now.Month &&
                                                               p.DateTime.Value.Year == DateTime.Now.Year);
            }

            return null;
        }

        public IQueryable<passengerflowbymin> GetRealTimePassengerflowbyMin(List<int> areaIDs)
        {
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID.HasValue && areaIDs.Contains(g.ParentID.Value));

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();
                DateTime dt = DateTime.Now.AddDays(-1);
                
                var passengerFlowbymin = this.ObjectContext.StatisticByMinsAndDate(5,
                                                                                   new DateTime(dt.Year, dt.Month, dt.Day, 23, 59, 59),
                                                                                   new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59)); ;

                return passengerFlowbymin.Where(p => gateIDs.Contains(p.gateID)).AsQueryable();
            }
            return null;
        }

        public IQueryable<passengerflowbymin> GetPassengerflowbyMinByTime(List<int> areaIDs, DateTime startTime, DateTime endTime)
        {
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID.HasValue && areaIDs.Contains(g.ParentID.Value));

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();


                var passengerFlowbymin = this.ObjectContext.StatisticByMinsAndDate(5, startTime, endTime);

                return passengerFlowbymin.Where(p => gateIDs.Contains(p.gateID)).AsQueryable();
            }
            return null;
        }

        public IQueryable<passengerflowbymin> GetPassengerflowbyHourByTime(List<int> areaIDs, DateTime startTime, DateTime endTime)
        {
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID.HasValue && areaIDs.Contains(g.ParentID.Value));

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();


                var passengerFlowbymin = this.ObjectContext.StatisticByMinsAndDate(60, startTime, endTime);

                return passengerFlowbymin.Where(p => gateIDs.Contains(p.gateID)).AsQueryable();
            }
            return null;
        }

        public IQueryable<passengerflowbymin> GetPassengerflowbySpecifiedTime(List<int> areaIDs, int mins, DateTime startTime, DateTime endTime)
        {
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID.HasValue && areaIDs.Contains(g.ParentID.Value));

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();


                var passengerFlowbymin = this.ObjectContext.StatisticByMinsAndDate(mins, startTime, endTime);

                return passengerFlowbymin.Where(p => gateIDs.Contains(p.gateID)).AsQueryable();
            }
            return null;
        }

        public IQueryable<passengerflowbyage> GetPassengerflowagebySpecifiedTime(List<int> areaIDs, DateTime startTime, DateTime endTime)
        {
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID.HasValue && areaIDs.Contains(g.ParentID.Value));

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();
                string gates = string.Empty;
                gateIDs.ForEach(g =>
                {
                    if (string.IsNullOrEmpty(gates))
                    {
                        gates = g.ToString();
                    }
                    else
                    {
                        gates = gates + "," + g.ToString();
                    }
                });

                var passengerFlowAgebymin = this.ObjectContext.SumPassengerflowsByAge(gates, startTime, endTime);

                return passengerFlowAgebymin.AsQueryable();
            }
            return null;

        }


        public IQueryable<posbymin> GetPosDataBySpecifiedTime(List<int> areaIDs, int mins, DateTime startTime, DateTime endTime)
        {
            var passengerFlowbymin = this.ObjectContext.StatisticsPosByMinsAndDate(mins, startTime, endTime);

            return passengerFlowbymin.Where(p => areaIDs.Contains(p.AreaID)).AsQueryable();
        }

        public IQueryable<passengerflowbymin> GetPassengerflowByYearMonth(List<int> areaIDs, int year, int month)
        {
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID.HasValue && areaIDs.Contains(g.ParentID.Value));

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();
                string gates = string.Empty;
                gateIDs.ForEach(g =>
                {
                    if (string.IsNullOrEmpty(gates))
                    {
                        gates = g.ToString();
                    }
                    else
                    {
                        gates = gates + "," + g.ToString();
                    }
                });

                DateTime startTime = new DateTime(year, month, 1);
                DateTime endTime = startTime.AddMonths(1);
                var passengerFlowbymin = this.ObjectContext.SumCountByGate(gates, startTime, endTime);
                
                this.ObjectContext.Connection.Close();
                DateTime lastStartTime = startTime.AddYears(-1);
                DateTime lastEndTime = endTime.AddYears(-1);
                var passengerFlowBymin2 = this.ObjectContext.SumCountByGate(gates, lastStartTime, lastEndTime);

                List<passengerflowbymin> passengers = passengerFlowbymin.ToList();
                if (passengerFlowBymin2.Count() > 0)
                {
                    passengerFlowBymin2.ToList().ForEach(p => { passengers.Add(p); });
                }

                return passengers.AsQueryable();
            }
            return null;
        }

        public IQueryable<passengerflowbydate> GetPassengerflowByYear(List<int> areaIDs, int year)
        {
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID.HasValue && areaIDs.Contains(g.ParentID.Value));

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();
                string gates = string.Empty;
                gateIDs.ForEach(g =>
                {
                    if (string.IsNullOrEmpty(gates))
                    {
                        gates = g.ToString();
                    }
                    else
                    {
                        gates = gates + "," + g.ToString();
                    }
                });

                DateTime startTime = new DateTime(year, 1, 1);
                DateTime endTime = startTime.AddYears(1);
                var passengerFlowbymin = this.ObjectContext.SumCountByGateandMonth(gates, startTime, endTime);
                List<passengerflowbydate> passengers = passengerFlowbymin.ToList();

                this.ObjectContext.Connection.Close();

                DateTime lastStartTime = startTime.AddYears(-1);
                DateTime lastEndTime = endTime.AddYears(-1);
                var passengerFlowBymin2 = this.ObjectContext.SumCountByGateandMonth(gates, lastStartTime, lastEndTime);

                if (passengerFlowBymin2.Count() > 0)
                {
                    passengerFlowBymin2.ToList().ForEach(p => { passengers.Add(p); });
                }

                return passengers.AsQueryable();
            }
            return null;
        }

        public IQueryable<passengerflowbydate> GetPassengerflowByMonthYear(List<int> areaIDs, int year1, int month1, int year2, int month2)
        {
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID.HasValue && areaIDs.Contains(g.ParentID.Value));

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();
                string gates = string.Empty;
                gateIDs.ForEach(g =>
                {
                    if (string.IsNullOrEmpty(gates))
                    {
                        gates = g.ToString();
                    }
                    else
                    {
                        gates = gates + "," + g.ToString();
                    }
                });

                DateTime startTime = new DateTime(year1, month1, 1);
                DateTime tempTime = new DateTime(year2,month2,1);
                DateTime endTime = tempTime.AddMonths(1);
                var passengerFlowbymin = this.ObjectContext.SumCountByGateandMonth(gates, startTime, endTime);
                List<passengerflowbydate> passengers = passengerFlowbymin.ToList();

                return passengers.AsQueryable();
            }
            return null;
        }

        public IQueryable<passengerflowbydate> GetPassengerflowByWeek(List<int> areaIDs, DateTime dateTime)
        {
            dateTime = new DateTime(dateTime.Year, dateTime.Month, dateTime.Day, 0, 0, 0);
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID.HasValue && areaIDs.Contains(g.ParentID.Value));

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();
                string gates = string.Empty;
                gateIDs.ForEach(g =>
                {
                    if (string.IsNullOrEmpty(gates))
                    {
                        gates = g.ToString();
                    }
                    else
                    {
                        gates = gates + "," + g.ToString();
                    }
                });

                DateTime startTime = dateTime;
                DateTime endTime = dateTime;
                GetWeekStartAndEndTime(dateTime, out startTime, out endTime);
                var passengerFlowbymin = this.ObjectContext.SumCountByGateandWeekDay(gates, startTime, endTime);
                List<passengerflowbydate> passengers = passengerFlowbymin.ToList();

                this.ObjectContext.Connection.Close();

                DateTime lastStartTime = dateTime.AddDays(-7);
                DateTime lastEndTime = dateTime.AddDays(-7);
                GetWeekStartAndEndTime(dateTime.AddDays(-7), out lastStartTime, out lastEndTime);
                var passengerFlowBymin2 = this.ObjectContext.SumCountByGateandWeekDay(gates, lastStartTime, lastEndTime);
                var temp2 = passengerFlowBymin2.ToList();
                if (temp2.Count > 0)
                {
                    temp2.ForEach(p => { passengers.Add(p); });
                }

                this.ObjectContext.Connection.Close();

                DateTime lastYearStartTime = dateTime.AddYears(-1);
                DateTime lastYearEndTime = dateTime.AddYears(-1);
                GetWeekStartAndEndTime(dateTime.AddYears(-1), out lastYearStartTime, out lastYearEndTime);
                var passengerFlowBymin3 = this.ObjectContext.SumCountByGateandWeekDay(gates, lastYearStartTime, lastYearEndTime);
                var temp3 = passengerFlowBymin3.ToList();
                if (temp3.Count > 0)
                {
                    temp3.ForEach(p => { passengers.Add(p); });
                }

                return passengers.AsQueryable();
            }
            return null;
        }

        public IQueryable<passengerflowbyage> GetPassengerFlowAgesByWeek(List<int> areaIDs, DateTime dateTime)
        {
            dateTime = new DateTime(dateTime.Year, dateTime.Month, dateTime.Day, 0, 0, 0);
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID.HasValue && areaIDs.Contains(g.ParentID.Value));

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();
                string gates = string.Empty;
                gateIDs.ForEach(g =>
                {
                    if (string.IsNullOrEmpty(gates))
                    {
                        gates = g.ToString();
                    }
                    else
                    {
                        gates = gates + "," + g.ToString();
                    }
                });

                DateTime startTime = dateTime;
                DateTime endTime = dateTime;
                GetWeekStartAndEndTime(dateTime, out startTime, out endTime);
                var passengerFlowbymin = this.ObjectContext.SumPassengerflowsByAge(gates, startTime, endTime);
                List<passengerflowbyage> passengers = passengerFlowbymin.ToList();

                this.ObjectContext.Connection.Close();

                DateTime lastStartTime = dateTime.AddDays(-7);
                DateTime lastEndTime = dateTime.AddDays(-7);
                GetWeekStartAndEndTime(dateTime.AddDays(-7), out lastStartTime, out lastEndTime);
                var passengerFlowBymin2 = this.ObjectContext.SumPassengerflowsByAge(gates, lastStartTime, lastEndTime);
                var temp2 = passengerFlowBymin2.ToList();
                if (temp2.Count > 0)
                {
                    temp2.ForEach(p => { passengers.Add(p); });
                }

                this.ObjectContext.Connection.Close();

                DateTime lastYearStartTime = dateTime.AddYears(-1);
                DateTime lastYearEndTime = dateTime.AddYears(-1);
                GetWeekStartAndEndTime(dateTime.AddYears(-1), out lastYearStartTime, out lastYearEndTime);
                var passengerFlowBymin3 = this.ObjectContext.SumPassengerflowsByAge(gates, lastYearStartTime, lastYearEndTime);
                var temp3 = passengerFlowBymin3.ToList();
                if (temp3.Count > 0)
                {
                    temp3.ForEach(p => { passengers.Add(p); });
                }

                return passengers.AsQueryable();
            }
            return null;
        }

        public IQueryable<passengerflowbydate> GetPassengerflowByMultiWeek(List<int> areaIDs, DateTime dateTime1, DateTime dateTime2)
        {
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID.HasValue && areaIDs.Contains(g.ParentID.Value));

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();
                string gates = string.Empty;
                gateIDs.ForEach(g =>
                {
                    if (string.IsNullOrEmpty(gates))
                    {
                        gates = g.ToString();
                    }
                    else
                    {
                        gates = gates + "," + g.ToString();
                    }
                });

                DateTime startTime = dateTime1;
                DateTime endTime = dateTime1;
                GetWeekStartAndEndTime(dateTime1, out startTime, out endTime);
                var passengerFlowbymin = this.ObjectContext.SumCountByGateandWeekDay(gates, startTime, endTime);
                List<passengerflowbydate> passengers = passengerFlowbymin.ToList();

                this.ObjectContext.Connection.Close();

                DateTime lastStartTime = dateTime2;
                DateTime lastEndTime = dateTime2;
                GetWeekStartAndEndTime(dateTime2, out lastStartTime, out lastEndTime);
                var passengerFlowBymin2 = this.ObjectContext.SumCountByGateandWeekDay(gates, lastStartTime, lastEndTime);
                var temp2 = passengerFlowBymin2.ToList();
                if (temp2.Count > 0)
                {
                    temp2.ForEach(p => { passengers.Add(p); });
                }

                

                return passengers.AsQueryable();
            }
            return null;
        }

        public IQueryable<passengerflowbydate> GetPassengerflowByYearWeek(List<int> areaIDs, int thisYear)
        {
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID.HasValue && areaIDs.Contains(g.ParentID.Value));

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();
                string gates = string.Empty;
                gateIDs.ForEach(g =>
                {
                    if (string.IsNullOrEmpty(gates))
                    {
                        gates = g.ToString();
                    }
                    else
                    {
                        gates = gates + "," + g.ToString();
                    }
                });

                DateTime startTime = new DateTime(thisYear, 1, 1, 0, 0, 0);
                DateTime endTime = startTime.AddYears(1);

                var passengerFlowbymin = this.ObjectContext.SumCountByGateandWeek(gates, startTime, endTime);
                List<passengerflowbydate> passengers = passengerFlowbymin.ToList();

                this.ObjectContext.Connection.Close();

                DateTime lastStartTime = startTime.AddYears(-1);
                DateTime lastEndTime = endTime.AddYears(-1);

                var passengerFlowBymin2 = this.ObjectContext.SumCountByGateandWeek(gates, lastStartTime, lastEndTime);

                if (passengerFlowBymin2.Count() > 0)
                {
                    passengerFlowBymin2.ToList().ForEach(p => { passengers.Add(p); });
                }
                return passengers.AsQueryable();
            }
            return null;
        }

        public IQueryable<passengerflowbydate> GetPassengerflowByMonth(List<int> areaIDs, int year, int month)
        {
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID.HasValue && areaIDs.Contains(g.ParentID.Value));

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();
                string gates = string.Empty;
                gateIDs.ForEach(g =>
                {
                    if (string.IsNullOrEmpty(gates))
                    {
                        gates = g.ToString();
                    }
                    else
                    {
                        gates = gates + "," + g.ToString();
                    }
                });

                DateTime startTime = new DateTime(year, month, 1);
                DateTime endTime = startTime.AddMonths(1);
                var passengerFlowbymin = this.ObjectContext.SumCountByGateandDay(gates, startTime, endTime);
                List<passengerflowbydate> passengers = passengerFlowbymin.ToList();

                this.ObjectContext.Connection.Close();
                
                DateTime lastStartTime = startTime.AddYears(-1);
                DateTime lastEndTime = endTime.AddYears(-1);
                var passengerFlowBymin2 = this.ObjectContext.SumCountByGateandDay(gates, lastStartTime, lastEndTime);

                if (passengerFlowBymin2.Count() > 0)
                {
                    passengerFlowBymin2.ToList().ForEach(p => {
                        passengers.Add(p); 
                    });
                }

                return passengers.AsQueryable();
            }
            return null;
        }

        public IQueryable<passengerflowbyage> GetPassengerAgeByMonth(List<int> areaIDs, int year, int month)
        {
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID.HasValue && areaIDs.Contains(g.ParentID.Value));

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();
                string gates = string.Empty;
                gateIDs.ForEach(g =>
                {
                    if (string.IsNullOrEmpty(gates))
                    {
                        gates = g.ToString();
                    }
                    else
                    {
                        gates = gates + "," + g.ToString();
                    }
                });

                DateTime startTime = new DateTime(year, month, 1);
                DateTime endTime = startTime.AddMonths(1);
                var passengerFlowbymin = this.ObjectContext.SumPassengerflowsByAge(gates, startTime, endTime);
                List<passengerflowbyage> passengers = passengerFlowbymin.ToList();

                this.ObjectContext.Connection.Close();

                DateTime lastStartTime = startTime.AddMonths(-1);
                DateTime lastEndTime = endTime.AddMonths(-1);
                var passengerFlowBymin2 = this.ObjectContext.SumPassengerflowsByAge(gates, lastStartTime, lastEndTime);
                var temp2 = passengerFlowBymin2.ToList();
                if (temp2.Count > 0)
                {
                    temp2.ForEach(p => { passengers.Add(p); });
                }

                this.ObjectContext.Connection.Close();

                DateTime lastYearStartTime = startTime.AddYears(-1);
                DateTime lastYearEndTime = endTime.AddYears(-1);
                var passengerFlowBymin3 = this.ObjectContext.SumPassengerflowsByAge(gates, lastYearStartTime, lastYearEndTime);

                if (passengerFlowBymin3.Count() > 0)
                {
                    passengerFlowBymin3.ToList().ForEach(p =>
                    {
                        passengers.Add(p);
                    });
                }

                return passengers.AsQueryable();
            }
            return null;
        }

        public IQueryable<passengerflowbydate> GetPassengerflowByMultiMonth(List<int> areaIDs, int year1, int month1, int year2, int month2)
        {
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID.HasValue && areaIDs.Contains(g.ParentID.Value));

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();
                string gates = string.Empty;
                gateIDs.ForEach(g =>
                {
                    if (string.IsNullOrEmpty(gates))
                    {
                        gates = g.ToString();
                    }
                    else
                    {
                        gates = gates + "," + g.ToString();
                    }
                });

                DateTime startTime = new DateTime(year1, month1, 1);
                DateTime endTime = startTime.AddMonths(1);
                var passengerFlowbymin = this.ObjectContext.SumCountByGateandDay(gates, startTime, endTime);
                List<passengerflowbydate> passengers = passengerFlowbymin.ToList();

                this.ObjectContext.Connection.Close();

                DateTime lastStartTime = new DateTime(year2, month2, 1);
                DateTime lastEndTime = lastStartTime.AddMonths(1);
                var passengerFlowBymin2 = this.ObjectContext.SumCountByGateandDay(gates, lastStartTime, lastEndTime);

                var temp2 = passengerFlowBymin2.ToList();
                if (temp2.Count > 0)
                {
                    temp2.ForEach(p => { passengers.Add(p); });
                }

                return passengers.AsQueryable();
            }
            return null;
        }

        public IQueryable<passengerflowbydate> GetPassengerflowByYearDay(List<int> areaIDs, int year)
        {
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID.HasValue && areaIDs.Contains(g.ParentID.Value));

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();
                string gates = string.Empty;
                gateIDs.ForEach(g =>
                {
                    if (string.IsNullOrEmpty(gates))
                    {
                        gates = g.ToString();
                    }
                    else
                    {
                        gates = gates + "," + g.ToString();
                    }
                });

                DateTime startTime = new DateTime(year, 1, 1, 0, 0, 0);
                DateTime endTime = startTime.AddYears(1);
                var passengerFlowbymin = this.ObjectContext.SumCountByGateandDay(gates, startTime, endTime);
                List<passengerflowbydate> passengers = passengerFlowbymin.ToList();

                return passengers.AsQueryable();
            }
            return null;
        }

        public IQueryable<passengerflowtimestamp> GetPassengerflowStamp(List<int> areaIDs, DateTime bizStartTime, DateTime bizEndTime, DateTime startTime, DateTime endTime)
        {
            var areaGates = this.ObjectContext.gates.Where(g => g.ParentID.HasValue && areaIDs.Contains(g.ParentID.Value));

            if (areaGates.Any())
            {
                var gateIDs = areaGates.Select(s => s.ID).ToList();
                string gates = string.Empty;
                gateIDs.ForEach(g =>
                {
                    if (string.IsNullOrEmpty(gates))
                    {
                        gates = g.ToString();
                    }
                    else
                    {
                        gates = gates + "," + g.ToString();
                    }
                });

                var passengerFlowbymin = this.ObjectContext.CalculatePassengerFlowTimeStamp(gates, bizStartTime, bizEndTime, startTime, endTime);
                List<passengerflowtimestamp> passengers = passengerFlowbymin.ToList();

                return passengers.AsQueryable();
            }
            return null;
        }

        [Ignore]
        private void GetWeekStartAndEndTime(DateTime dateTime, out DateTime startTime, out DateTime endTime)
        {
            startTime = dateTime;
            endTime = dateTime;
            switch (dateTime.DayOfWeek)
            {
                case DayOfWeek.Sunday:
                    endTime = dateTime.AddDays(1);
                    startTime = dateTime.AddDays(-6);
                    break;
                case DayOfWeek.Monday:
                    startTime = dateTime;
                    endTime = dateTime.AddDays(7);
                    break;
                case DayOfWeek.Tuesday:
                    startTime = dateTime.AddDays(-1);
                    endTime = dateTime.AddDays(6);
                    break;
                case DayOfWeek.Wednesday:
                    startTime = dateTime.AddDays(-2);
                    endTime = dateTime.AddDays(5);
                    break;
                case DayOfWeek.Thursday:
                    startTime = dateTime.AddDays(-3);
                    endTime = dateTime.AddDays(4);
                    break;
                case DayOfWeek.Friday:
                    startTime = dateTime.AddDays(-4);
                    endTime = dateTime.AddDays(3);
                    break;
                case DayOfWeek.Saturday:
                    startTime = dateTime.AddDays(-5);
                    endTime = dateTime.AddDays(2);
                    break;
            }
        }

        public void InsertPassengerflow(passengerflow passengerflow)
        {
            if ((passengerflow.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(passengerflow, EntityState.Added);
            }
            else
            {
                this.ObjectContext.passengerflows.AddObject(passengerflow);
            }
        }

        public void UpdatePassengerflow(passengerflow currentpassengerflow)
        {
            this.ObjectContext.passengerflows.AttachAsModified(currentpassengerflow, this.ChangeSet.GetOriginal(currentpassengerflow));
        }

        public void DeletePassengerflow(passengerflow passengerflow)
        {
            if ((passengerflow.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(passengerflow, EntityState.Deleted);
            }
            else
            {
                this.ObjectContext.passengerflows.Attach(passengerflow);
                this.ObjectContext.passengerflows.DeleteObject(passengerflow);
            }
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'pos' query.
        [Query(IsDefault = true)]
        public IQueryable<pos> GetPos()
        {
            return this.ObjectContext.pos;
        }

        public IQueryable<pos> GetPosByDate(int shopID, DateTime dateTime)
        {
            DateTime time = new DateTime(dateTime.Year, dateTime.Month, dateTime.Day);
            return this.ObjectContext.pos.Include("area").Where(p => p.TradeDate.Value.Equals(time) && p.AreaID == shopID);
        }

        public void InsertPos(pos pos)
        {
            if ((pos.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(pos, EntityState.Added);
            }
            else
            {
                this.ObjectContext.pos.AddObject(pos);
            }
        }

        public void UpdatePos(pos currentpos)
        {
            this.ObjectContext.pos.AttachAsModified(currentpos, this.ChangeSet.GetOriginal(currentpos));
        }

        public void DeletePos(pos pos)
        {
            if ((pos.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(pos, EntityState.Deleted);
            }
            else
            {
                this.ObjectContext.pos.Attach(pos);
                this.ObjectContext.pos.DeleteObject(pos);
            }
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'roles' query.
        [Query(IsDefault = true)]
        public IQueryable<role> GetRoles()
        {
            return this.ObjectContext.roles.Include("users");
        }

        public void InsertRole(role role)
        {
            if ((role.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(role, EntityState.Added);
            }
            else
            {
                this.ObjectContext.roles.AddObject(role);
            }
        }

        public void UpdateRole(role currentrole)
        {
            this.ObjectContext.roles.AttachAsModified(currentrole, this.ChangeSet.GetOriginal(currentrole));
        }

        public void DeleteRole(role role)
        {
            if ((role.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(role, EntityState.Deleted);
            }
            else
            {
                this.ObjectContext.roles.Attach(role);
                this.ObjectContext.roles.DeleteObject(role);
            }
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'users' query.
        [Query(IsDefault = true)]
        public IQueryable<user> GetUsers()
        {
            return this.ObjectContext.users.Include("role");
        }

        public user GetUserInfo(string name,string pwd)
        {
            return this.ObjectContext.users.Include("role").Where(u => u.Name.Equals(name, StringComparison.OrdinalIgnoreCase) && 
                                                       u.Password.Equals(pwd, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
        }

        public void InsertUser(user user)
        {
            if ((user.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(user, EntityState.Added);
            }
            else
            {
                this.ObjectContext.users.AddObject(user);
            }
        }

        public void UpdateUser(user currentuser)
        {
            this.ObjectContext.users.AttachAsModified(currentuser, this.ChangeSet.GetOriginal(currentuser));
        }

        public void DeleteUser(user user)
        {
            if ((user.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(user, EntityState.Deleted);
            }
            else
            {
                this.ObjectContext.users.Attach(user);
                this.ObjectContext.users.DeleteObject(user);
            }
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'vaservers' query.
        [Query(IsDefault = true)]
        public IQueryable<vaserver> GetVaservers()
        {
            return this.ObjectContext.vaservers.Include("vatype").Include("cameras");
        }

        public void InsertVaserver(vaserver vaserver)
        {
            if ((vaserver.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(vaserver, EntityState.Added);
            }
            else
            {
                this.ObjectContext.vaservers.AddObject(vaserver);
            }
        }

        public void UpdateVaserver(vaserver currentvaserver)
        {
            this.ObjectContext.vaservers.AttachAsModified(currentvaserver, this.ChangeSet.GetOriginal(currentvaserver));
        }

        public void DeleteVaserver(vaserver vaserver)
        {
            if ((vaserver.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(vaserver, EntityState.Deleted);
            }
            else
            {
                this.ObjectContext.vaservers.Attach(vaserver);
                this.ObjectContext.vaservers.DeleteObject(vaserver);
            }
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'vatypes' query.
        [Query(IsDefault = true)]
        public IQueryable<vatype> GetVatypes()
        {
            return this.ObjectContext.vatypes;
        }

        public void InsertVatype(vatype vatype)
        {
            if ((vatype.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(vatype, EntityState.Added);
            }
            else
            {
                this.ObjectContext.vatypes.AddObject(vatype);
            }
        }

        public void UpdateVatype(vatype currentvatype)
        {
            this.ObjectContext.vatypes.AttachAsModified(currentvatype, this.ChangeSet.GetOriginal(currentvatype));
        }

        public void DeleteVatype(vatype vatype)
        {
            if ((vatype.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(vatype, EntityState.Deleted);
            }
            else
            {
                this.ObjectContext.vatypes.Attach(vatype);
                this.ObjectContext.vatypes.DeleteObject(vatype);
            }
        }
    }
}


